package com.demo.las.repository;

import com.demo.las.entity.KnowledgeAsset;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Repository
public interface KnowledgeAssetRepository extends JpaRepository<KnowledgeAsset, Long> {
    
    List<KnowledgeAsset> findByUserIdOrderByAssetValueDesc(Long userId);
    
    Optional<KnowledgeAsset> findByUserIdAndSubject(Long userId, String subject);
    
    List<KnowledgeAsset> findByUserIdAndMasteryLevelGreaterThan(Long userId, BigDecimal minMasteryLevel);
    
    List<KnowledgeAsset> findByUserIdAndMasteryLevelBetween(Long userId, BigDecimal minMasteryLevel, BigDecimal maxMasteryLevel);
    
    @Query("SELECT SUM(ka.assetValue) FROM KnowledgeAsset ka WHERE ka.userId = :userId")
    BigDecimal getTotalAssetValueByUser(@Param("userId") Long userId);
    
    @Query("SELECT SUM(ka.currentValue) FROM KnowledgeAsset ka WHERE ka.userId = :userId")
    BigDecimal getTotalCurrentValueByUser(@Param("userId") Long userId);
    
    @Query("SELECT AVG(ka.masteryLevel) FROM KnowledgeAsset ka WHERE ka.userId = :userId")
    BigDecimal getAverageMasteryLevelByUser(@Param("userId") Long userId);
    
    @Query("SELECT ka.subject, ka.masteryLevel FROM KnowledgeAsset ka WHERE ka.userId = :userId ORDER BY ka.masteryLevel DESC")
    List<Object[]> getMasteryLevelBySubject(@Param("userId") Long userId);
    
    @Query("SELECT ka.subject, ka.assetValue, ka.currentValue, ka.masteryLevel FROM KnowledgeAsset ka WHERE ka.userId = :userId ORDER BY ka.assetValue DESC")
    List<Object[]> getAssetOverviewByUser(@Param("userId") Long userId);
    
    @Query("SELECT ka FROM KnowledgeAsset ka WHERE ka.userId = :userId AND ka.lastStudyDate IS NOT NULL ORDER BY ka.lastStudyDate DESC")
    List<KnowledgeAsset> findRecentlyStudiedAssets(@Param("userId") Long userId);
    
    @Query("SELECT ka FROM KnowledgeAsset ka WHERE ka.userId = :userId AND (ka.lastStudyDate IS NULL OR ka.lastStudyDate < :sevenDaysAgo)")
    List<KnowledgeAsset> findAssetsNeedReview(@Param("userId") Long userId, @Param("sevenDaysAgo") LocalDate sevenDaysAgo);
    
    @Query("SELECT COUNT(*) FROM KnowledgeAsset ka WHERE ka.userId = :userId AND ka.masteryLevel >= :minLevel")
    Long countAssetsAboveMasteryLevel(@Param("userId") Long userId, @Param("minLevel") BigDecimal minLevel);
}
